package org.example;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.ss.usermodel.DataFormatter;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelFormatChecker {
    private static double totalScore = 0;
    private static List<String> feedback = new ArrayList<>();
    private static List<String> losePoints = new ArrayList<>(); // 新增：失分点列表

    // 定义标准颜色值
    private static final byte[] DARK_BLUE_RGB = {0, 32, 96}; // #002060
    private static final byte[] ORANGE_RGB = {(byte)255, (byte)192, 0}; // #ffc000

    public static void main(String[] args) {
        try {
            // 读取Excel文件
            long startTime =System.currentTimeMillis();
            FileInputStream file = new FileInputStream("C:\\Users\\Lenovo\\Desktop\\Book.xlsx");
            Workbook workbook = new XSSFWorkbook(file);

            // 检查成绩单工作表
            Sheet scoreSheet = workbook.getSheet("成绩单");
            if (scoreSheet == null) {
                scoreSheet = workbook.getSheetAt(0);
            }

            // 执行评分
            gradeExcel(workbook, scoreSheet);

            // 输出JSON格式的评分结果
            System.out.println("{");
            System.out.println("  \"score\": " + totalScore + ",");
            System.out.println("  \"losePoints\": [");
            for (int i = 0; i < losePoints.size(); i++) {
                System.out.println("    \"" + losePoints.get(i) + "\"" + (i < losePoints.size() - 1 ? "," : ""));
            }
            System.out.println("  ]");
            System.out.println("}");

            workbook.close();
            file.close();
            long endTime = System.currentTimeMillis();
            System.out.println("\n耗时:"+(endTime - startTime)+"毫秒");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static void gradeExcel(Workbook workbook, Sheet scoreSheet) {
        // (1) 检查标题、合并单元格、插入列和序号填充 (2分)
        checkRequirement1(scoreSheet);

        // (2) 检查边框、列宽行高、数字格式 (2分)
        checkRequirement2(scoreSheet);

        // (3) 检查总分平均分计算和工作表复制 (2分)
        checkRequirement3(workbook, scoreSheet);

        // (4) 检查排序和分类汇总 (3分)
        checkRequirement4(workbook);
    }

    private static void checkRequirement1(Sheet sheet) {
        double score = 0;
        List<String> reqFeedback = new ArrayList<>();

        // 检查A1:L1合并居中
        boolean merged = false;
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress region = sheet.getMergedRegion(i);
            if (region.getFirstRow() == 0 && region.getLastRow() == 0 &&
                    region.getFirstColumn() == 0 && region.getLastColumn() == 12) { // A1:L1
                merged = true;
                break;
            }
        }

        if (merged) {
            score += 0.5;
            reqFeedback.add("✓ A1:L1合并居中正确");
        } else {
            reqFeedback.add("✗ 合并居中错误");
            losePoints.add("A1:L1合并居中错误");
        }

        // 检查标题文字和格式
        Row row0 = sheet.getRow(0);
        if (row0 != null) {
            Cell cellA1 = row0.getCell(0);
            if (cellA1 != null) {
                String title = getCellValueAsString(cellA1);
                if ("初二年级第一学期期末成绩单".equals(title)) {
                    score += 0.2;
                    reqFeedback.add("✓ 标题文字正确");
                } else {
                    reqFeedback.add("✗ 标题文字错误");
                    losePoints.add("标题文字错误");
                }

                // 检查样式
                CellStyle style = cellA1.getCellStyle();
                if (style != null) {
                    // 检查填充色（深蓝 #002060）
                    boolean fillColorCorrect = checkExactColorFill(style, DARK_BLUE_RGB);
                    if (fillColorCorrect) {
                        score += 0.2;
                        reqFeedback.add("✓ 填充色为深蓝色 (#002060)");
                    } else {
                        reqFeedback.add("✗ 填充色不是深蓝色 (#002060)");
                        losePoints.add("标题填充色不是深蓝色");
                    }

                    // 检查字体
                    Font font = sheet.getWorkbook().getFontAt(style.getFontIndex());
                    if (font != null) {
                        if ("黑体".equals(font.getFontName()) || "SimHei".equals(font.getFontName())) {
                            score += 0.2;
                            reqFeedback.add("✓ 字体为黑体");
                        } else {
                            reqFeedback.add("✗ 字体不是黑体");
                            losePoints.add("标题字体不是黑体");
                        }

                        // 检查文字颜色（橙色 #ffc000）
                        boolean textColorCorrect = checkExactTextColor(font, ORANGE_RGB);
                        if (textColorCorrect) {
                            score += 0.2;
                            reqFeedback.add("✓ 文字颜色为橙色 (#ffc000)");
                        } else {
                            reqFeedback.add("✗ 文字颜色不是橙色 (#ffc000)");
                            losePoints.add("标题文字颜色不是橙色");
                        }

                        if (font.getFontHeightInPoints() == 16) {
                            score += 0.2;
                            reqFeedback.add("✓ 字号为16号");
                        } else {
                            reqFeedback.add("✗ 字号不是16号");
                            losePoints.add("标题字号不是16号");
                        }
                    }
                }
            }
        }

        // 检查插入列和序号
        Row row2 = sheet.getRow(2); // 第3行
        if (row2 != null) {
            Cell b3Cell = row2.getCell(1); // B3单元格
            if (b3Cell != null && "序号".equals(getCellValueAsString(b3Cell))) {
                score += 0.2;
                reqFeedback.add("✓ B3单元格标题正确");
            } else {
                reqFeedback.add("✗ B3单元格标题错误");
                losePoints.add("B3单元格标题错误");
            }
        }

        // 检查序号序列（从1到18）
        boolean sequenceCorrect = true;
        int expectedNumber = 1;
        for (int i = 3; i <= 20; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                Cell cell = row.getCell(1); // B列
                if (cell == null) {
                    sequenceCorrect = false;
                    break;
                }

                String cellValue = getCellValueAsString(cell);
                try {
                    int num = Integer.parseInt(cellValue.trim());
                    if (num != expectedNumber) {
                        sequenceCorrect = false;
                        break;
                    }
                    expectedNumber++;
                } catch (NumberFormatException e) {
                    sequenceCorrect = false;
                    break;
                }
            } else {
                sequenceCorrect = false;
                break;
            }
        }

        if (sequenceCorrect) {
            score += 0.3;
            reqFeedback.add("✓ 序号填充正确");
        } else {
            reqFeedback.add("✗ 序号填充错误");
            losePoints.add("序号填充错误");
        }

        totalScore += Math.min(score, 2);
        feedback.add("(1) 标题和序号检查: " + Math.min(score, 2) + "/2分");
        feedback.addAll(reqFeedback);
    }

    // 精确颜色检查方法
    private static boolean checkExactColorFill(CellStyle style, byte[] expectedRGB) {
        try {
            // 检查索引颜色（如果匹配标准颜色）
            if (style.getFillForegroundColor() == IndexedColors.DARK_BLUE.getIndex() &&
                    expectedRGB == DARK_BLUE_RGB) {
                return true;
            }

            // 检查XSSF颜色
            if (style instanceof org.apache.poi.xssf.usermodel.XSSFCellStyle) {
                org.apache.poi.xssf.usermodel.XSSFCellStyle xssfStyle =
                        (org.apache.poi.xssf.usermodel.XSSFCellStyle) style;
                XSSFColor color = xssfStyle.getFillForegroundColorColor();
                if (color != null) {
                    return compareColors(color, expectedRGB);
                }
            }
        } catch (Exception e) {
            // 忽略异常
        }
        return false;
    }

    private static boolean checkExactTextColor(Font font, byte[] expectedRGB) {
        try {
            // 检查索引颜色（如果匹配标准颜色）
            if (font.getColor() == IndexedColors.ORANGE.getIndex() &&
                    expectedRGB == ORANGE_RGB) {
                return true;
            }

            // 检查XSSF颜色
            if (font instanceof org.apache.poi.xssf.usermodel.XSSFFont) {
                org.apache.poi.xssf.usermodel.XSSFFont xssfFont =
                        (org.apache.poi.xssf.usermodel.XSSFFont) font;
                XSSFColor color = xssfFont.getXSSFColor();
                if (color != null) {
                    return compareColors(color, expectedRGB);
                }
            }
        } catch (Exception e) {
            // 忽略异常
        }
        return false;
    }

    // 比较颜色是否匹配
    private static boolean compareColors(XSSFColor actualColor, byte[] expectedRGB) {
        try {
            // 首先尝试获取RGB
            byte[] actualRGB = actualColor.getRGB();
            if (actualRGB != null && actualRGB.length >= 3) {
                return actualRGB[0] == expectedRGB[0] &&
                        actualRGB[1] == expectedRGB[1] &&
                        actualRGB[2] == expectedRGB[2];
            }

            // 如果无法获取RGB，尝试获取ARGB
            byte[] actualARGB = actualColor.getARGB();
            if (actualARGB != null && actualARGB.length >= 4) {
                // ARGB格式：Alpha, Red, Green, Blue
                return actualARGB[1] == expectedRGB[0] &&
                        actualARGB[2] == expectedRGB[1] &&
                        actualARGB[3] == expectedRGB[2];
            }
        } catch (Exception e) {
            // 忽略异常
        }
        return false;
    }

    private static void checkRequirement2(Sheet sheet) {
        double score = 0;
        List<String> reqFeedback = new ArrayList<>();

        // 检查边框 - 放宽检查条件
        boolean bordersCorrect = true;
        int borderedCells = 0;
        int totalCells = 0;

        for (int rowNum = 2; rowNum <= 20; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                bordersCorrect = false;
                continue;
            }

            for (int colNum = 0; colNum <= 12; colNum++) {
                totalCells++;
                Cell cell = row.getCell(colNum);
                if (cell != null) {
                    CellStyle style = cell.getCellStyle();
                    if (style != null) {
                        // 检查是否有边框（不一定是细线，只要有边框就算正确）
                        if (style.getBorderTop() != BorderStyle.NONE ||
                                style.getBorderBottom() != BorderStyle.NONE ||
                                style.getBorderLeft() != BorderStyle.NONE ||
                                style.getBorderRight() != BorderStyle.NONE) {
                            borderedCells++;
                        }
                    }
                }
            }
        }

        // 如果超过80%的单元格有边框，就认为边框设置正确
        if (borderedCells >= totalCells * 0.8) {
            score += 0.5;
            reqFeedback.add("✓ 边框设置正确");
        } else {
            reqFeedback.add("✗ 边框设置错误");
            losePoints.add("边框设置错误");
        }

        // 检查列宽（放宽条件）
        boolean columnWidthCorrect = true;
        for (int i = 0; i <= 12; i++) {
            int width = sheet.getColumnWidth(i);
            // 9字符大约为9 * 256=2304，允许较大误差
            if (Math.abs(width - 9 * 256) > 1000) {
                columnWidthCorrect = false;
                break;
            }
        }

        if (columnWidthCorrect) {
            score += 0.5;
            reqFeedback.add("✓ 列宽设置正确");
        } else {
            reqFeedback.add("✗ 列宽设置错误");
            losePoints.add("列宽设置错误");
        }

        // 检查行高（放宽条件）
        boolean rowHeightCorrect = true;
        for (int i = 2; i <= 20; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                // 行高以磅为单位，20磅约为400缇，允许较大误差
                if (Math.abs(row.getHeightInPoints() - 20) > 5) {
                    rowHeightCorrect = false;
                    break;
                }
            } else {
                rowHeightCorrect = false;
                break;
            }
        }

        if (rowHeightCorrect) {
            score += 0.5;
            reqFeedback.add("✓ 行高设置正确");
        } else {
            reqFeedback.add("✗ 行高设置错误");
            losePoints.add("行高设置错误");
        }

        // 改进的数字格式检查 - 修复小数位数检测问题
        boolean numberFormatCorrect = true;
        boolean textFormatCorrect = true;
        DataFormatter dataFormatter = new DataFormatter();

        for (int rowNum = 3; rowNum <= 20; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) continue;

            // 检查E4:M21数值格式 - 严格检查小数位数
            for (int colNum = 4; colNum <= 12; colNum++) {
                Cell cell = row.getCell(colNum);
                if (cell != null && (cell.getCellType() == CellType.NUMERIC ||
                        (cell.getCellType() == CellType.FORMULA && cell.getCachedFormulaResultType() == CellType.NUMERIC))) {

                    // 方法1: 检查数据格式字符串
                    CellStyle style = cell.getCellStyle();
                    if (style != null) {
                        String dataFormat = style.getDataFormatString();
                        // 检查格式字符串是否包含两位小数模式
                        if (dataFormat != null && !dataFormat.matches(".*0\\.00.*") &&
                                !dataFormat.matches(".*#\\.00.*") &&
                                !dataFormat.matches(".*\\?\\?/\\?\\?.*")) {

                            // 方法2: 检查实际显示的小数位数
                            String formattedValue = dataFormatter.formatCellValue(cell);
                            if (formattedValue.contains(".")) {
                                String decimalPart = formattedValue.split("\\.")[1];
                                if (decimalPart.length() != 2) {
                                    numberFormatCorrect = false;
                                    break;
                                }
                            } else {
                                // 如果没有小数点，说明没有小数部分，不符合两位小数的要求
                                numberFormatCorrect = false;
                                break;
                            }
                        }
                    } else {
                        numberFormatCorrect = false;
                        break;
                    }
                }
            }
            if (!numberFormatCorrect) break;

            // 检查B列文本格式
            Cell bCell = row.getCell(1);
            if (bCell != null) {
                // 检查单元格类型是否为文本，或者样式为文本格式
                if (bCell.getCellType() == CellType.STRING) {
                    // 已经是文本类型
                } else if (bCell.getCellType() == CellType.NUMERIC) {
                    CellStyle style = bCell.getCellStyle();
                    if (style != null) {
                        String dataFormat = style.getDataFormatString();
                        // 检查是否是文本格式
                        if (dataFormat == null || !dataFormat.contains("@")) {
                            textFormatCorrect = false;
                            break;
                        }
                    } else {
                        textFormatCorrect = false;
                        break;
                    }
                } else {
                    textFormatCorrect = false;
                    break;
                }
            } else {
                textFormatCorrect = false;
                break;
            }
        }

        if (numberFormatCorrect) {
            score += 0.3;
            reqFeedback.add("✓ E4:M21数值格式正确（保留两位小数）");
        } else {
            reqFeedback.add("✗ E4:M21数值格式错误 - 应为数值格式且保留两位小数");
            losePoints.add("E4:M21数值格式错误 - 应为数值格式且保留两位小数");
        }

        if (textFormatCorrect) {
            score += 0.2;
            reqFeedback.add("✓ B列文本格式正确");
        } else {
            reqFeedback.add("✗ B列文本格式错误");
            losePoints.add("B列文本格式错误");
        }

        totalScore += Math.min(score, 2);
        feedback.add("(2) 格式设置检查: " + Math.min(score, 2) + "/2分");
        feedback.addAll(reqFeedback);
    }

    private static void checkRequirement3(Workbook workbook, Sheet scoreSheet) {
        double score = 0;
        List<String> reqFeedback = new ArrayList<>();

        // 改进的总分和平均分计算检查
        boolean allTotalCorrect = true;
        boolean allAverageCorrect = true;
        int checkedRows = 0;

        for (int rowNum = 3; rowNum <= 20; rowNum++) {
            Row row = scoreSheet.getRow(rowNum);
            if (row == null) continue;

            checkedRows++;

            // 计算期望的总分（科目在E-K列，索引4-10）
            double expectedTotal = 0;
            int subjectCount = 0;
            for (int colNum = 4; colNum <= 10; colNum++) {
                Cell cell = row.getCell(colNum);
                if (cell != null && (cell.getCellType() == CellType.NUMERIC ||
                        (cell.getCellType() == CellType.FORMULA && cell.getCachedFormulaResultType() == CellType.NUMERIC))) {
                    expectedTotal += cell.getNumericCellValue();
                    subjectCount++;
                }
            }

            // 检查实际总分（L列，索引11）
            Cell totalCell = row.getCell(11);
            if (totalCell != null) {
                double actualTotal = 0;
                if (totalCell.getCellType() == CellType.NUMERIC) {
                    actualTotal = totalCell.getNumericCellValue();
                } else if (totalCell.getCellType() == CellType.FORMULA &&
                        totalCell.getCachedFormulaResultType() == CellType.NUMERIC) {
                    actualTotal = totalCell.getNumericCellValue();
                }

                // 检查数值是否匹配（放宽误差范围）
                if (Math.abs(actualTotal - expectedTotal) > 0.01) {
                    allTotalCorrect = false;
                }
            } else {
                allTotalCorrect = false;
            }

            // 检查实际平均分（M列，索引12）
            Cell avgCell = row.getCell(12);
            double expectedAvg = subjectCount > 0 ? expectedTotal / subjectCount : 0;
            if (avgCell != null) {
                double actualAvg = 0;
                if (avgCell.getCellType() == CellType.NUMERIC) {
                    actualAvg = avgCell.getNumericCellValue();
                } else if (avgCell.getCellType() == CellType.FORMULA &&
                        avgCell.getCachedFormulaResultType() == CellType.NUMERIC) {
                    actualAvg = avgCell.getNumericCellValue();
                }

                // 检查数值是否匹配（放宽误差范围）
                if (Math.abs(actualAvg - expectedAvg) > 0.01) {
                    allAverageCorrect = false;
                }
            } else {
                allAverageCorrect = false;
            }
        }

        // 所有行的总分计算必须全部正确
        if (checkedRows > 0 && allTotalCorrect) {
            score += 0.5;
            reqFeedback.add("✓ 总分计算正确");
        } else {
            reqFeedback.add("✗ 总分计算错误");
            losePoints.add("总分计算错误");
        }

        // 所有行的平均分计算必须全部正确
        if (checkedRows > 0 && allAverageCorrect) {
            score += 0.5;
            reqFeedback.add("✓ 平均分计算正确");
        } else {
            reqFeedback.add("✗ 平均分计算错误");
            losePoints.add("平均分计算错误");
        }

        // 检查工作表复制和重命名
        Sheet copySheet = workbook.getSheet("分类汇总");
        if (copySheet != null) {
            score += 1.0;
            reqFeedback.add("✓ 工作表复制和重命名正确");
        } else {
            reqFeedback.add("✗ 工作表复制和重命名错误");
            losePoints.add("工作表复制和重命名错误");
        }

        totalScore += Math.min(score, 2);
        feedback.add("(3) 计算和工作表操作检查: " + Math.min(score, 2) + "/2分");
        feedback.addAll(reqFeedback);
    }

    private static void checkRequirement4(Workbook workbook) {
        double score = 0;
        List<String> reqFeedback = new ArrayList<>();

        Sheet summarySheet = workbook.getSheet("分类汇总");
        if (summarySheet == null) {
            feedback.add("(4) 分类汇总检查: 0/3分 - 未找到分类汇总工作表");
            losePoints.add("未找到分类汇总工作表");
            return;
        }

        // 检查排序
        boolean hasSorting = checkSorting(summarySheet);
        if (hasSorting) {
            score += 1.0;
            reqFeedback.add("✓ 排序基本正确");
        } else {
            reqFeedback.add("✗ 排序可能错误");
            losePoints.add("排序错误");
        }

        // 检查分类汇总
        boolean hasSubtotal = checkSubtotal(summarySheet);
        if (hasSubtotal) {
            score += 2.0;
            reqFeedback.add("✓ 分类汇总设置正确");
        } else {
            reqFeedback.add("✗ 分类汇总设置错误");
            losePoints.add("分类汇总设置错误");
        }

        totalScore += Math.min(score, 3);
        feedback.add("(4) 排序和分类汇总检查: " + Math.min(score, 3) + "/3分");
        feedback.addAll(reqFeedback);
    }

    private static boolean checkSorting(Sheet sheet) {
        int classCol = findClassColumn(sheet);
        int totalCol = findTotalColumn(sheet);

        if (classCol == -1 || totalCol == -1) {
            System.err.println("无法找到班级列或总分列");
            return false;
        }

//        System.err.println("班级列: " + classCol + ", 总分列: " + totalCol);

        String prevClass = "";
        double prevTotal = -1;
        boolean first = true;

        for (int i = 3; i <= 20; i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;

            // 跳过汇总行
            Cell firstCell = row.getCell(0);
            if (firstCell != null) {
                String firstCellValue = getCellValueAsString(firstCell);
                if (firstCellValue != null && firstCellValue.contains("平均值")) {
                    continue;
                }
            }

            Cell classCell = row.getCell(classCol);
            Cell totalCell = row.getCell(totalCol);

            if (classCell != null && totalCell != null) {
                String currentClass = getCellValueAsString(classCell);
                double currentTotal = 0;

                try {
                    currentTotal = totalCell.getNumericCellValue();
                } catch (Exception e) {
                    // 如果无法直接获取数值，尝试从字符串解析
                    String totalStr = getCellValueAsString(totalCell);
                    try {
                        currentTotal = Double.parseDouble(totalStr);
                    } catch (NumberFormatException ex) {
//                        System.err.println("无法解析总分: " + totalStr);
                        return false;
                    }
                }

                if (!first) {
                    // 检查班级排序（升序）
                    int classCompare = currentClass.compareTo(prevClass);
                    if (classCompare < 0) {
                        // 班级不是升序
//                        System.err.println("班级排序错误: " + prevClass + " -> " + currentClass);
                        return false;
                    } else if (classCompare == 0) {
                        // 班级相同，检查总分排序（降序）
                        if (currentTotal > prevTotal) {
                            // 总分不是降序
//                            System.err.println("同班级内总分排序错误: " + prevTotal + " -> " + currentTotal);
                            return false;
                        }
                    }
                }

                prevClass = currentClass;
                prevTotal = currentTotal;
                first = false;

//                System.err.println("行 " + i + ": 班级=" + currentClass + ", 总分=" + currentTotal);
            }
        }

        return true;
    }

    private static int findTotalColumn(Sheet sheet) {
        Row headerRow = sheet.getRow(2);
        if (headerRow == null) {
            // 如果第3行没有标题，尝试其他行
            for (int i = 0; i <= 5; i++) {
                headerRow = sheet.getRow(i);
                if (headerRow != null) break;
            }
        }

        if (headerRow == null) return -1;

        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            Cell cell = headerRow.getCell(i);
            if (cell != null) {
                String value = getCellValueAsString(cell);
                if ("总分".equals(value) || "total".equalsIgnoreCase(value) || "合计".equals(value)) {
                    return i;
                }
            }
        }

        return -1;
    }

    private static boolean checkSubtotal(Sheet sheet) {
        // 查找汇总行并检查是否对所有七门课程进行了汇总
        int classSubtotalCount = 0;
        boolean allSubjectsIncluded = true;

        // 七门课程的列索引（语文、数学、英语、物理、化学、生物、政治）
        int[] subjectCols = {4, 5, 6, 7, 8, 9, 10}; // E-K列

        // 检查整个工作表，不限于特定行范围
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;

            Cell firstCell = row.getCell(0);
            if (firstCell == null) continue;

            String firstCellValue = getCellValueAsString(firstCell);
            if (firstCellValue == null) continue;

            // 检查是否是班级汇总行（例如：1平均值，2平均值，3平均值）
            boolean isClassSubtotal = firstCellValue.matches(".*[123]\\s*平均值.*");
            // 检查是否是总平均值行
            boolean isTotalAverage = firstCellValue.contains("总平均值");

            if (isClassSubtotal || isTotalAverage) {
//                System.err.println("找到汇总行，行号: " + i + ", 第一个单元格值: " + firstCellValue);
                // 如果是班级汇总行，则计数
                if (isClassSubtotal) {
                    classSubtotalCount++;
                }

                // 检查这行是否对所有七门课程都有平均值
                for (int col : subjectCols) {
                    Cell cell = row.getCell(col);
                    if (cell == null) {
                        allSubjectsIncluded = false;
//                        System.err.println("汇总行 " + firstCellValue + " 缺少科目列 " + col);
                        break;
                    }

                    // 检查单元格是否有值（平均值）
                    String value = getCellValueAsString(cell);
                    if (value == null || value.trim().isEmpty()) {
                        allSubjectsIncluded = false;
//                        System.err.println("汇总行 " + firstCellValue + " 科目列 " + col + " 值为空");
                        break;
                    }

                    // 检查是否是数值
                    try {
                        Double.parseDouble(value);
                    } catch (NumberFormatException e) {
                        allSubjectsIncluded = false;
//                        System.err.println("汇总行 " + firstCellValue + " 科目列 " + col + " 值不是数值: " + value);
                        break;
                    }
                }
            }
        }

//        System.err.println("班级汇总行数量: " + classSubtotalCount + ", 所有科目都被汇总: " + allSubjectsIncluded);

        // 应该至少有3个班级的汇总行，且所有科目都被汇总
        return classSubtotalCount >= 3 && allSubjectsIncluded;
    }

    private static int findClassColumn(Sheet sheet) {
        Row headerRow = sheet.getRow(2);
        if (headerRow == null) return -1;

        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            Cell cell = headerRow.getCell(i);
            if (cell != null) {
                String value = getCellValueAsString(cell);
                if ("班级".equals(value)) {
                    return i;
                }
            }
        }
        return -1;
    }

    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    double value = cell.getNumericCellValue();
                    if (value == Math.floor(value)) {
                        return String.valueOf((int) value);
                    } else {
                        return String.format("%.2f", value);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    switch (cell.getCachedFormulaResultType()) {
                        case STRING:
                            return cell.getStringCellValue();
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                return cell.getDateCellValue().toString();
                            } else {
                                double value = cell.getNumericCellValue();
                                if (value == Math.floor(value)) {
                                    return String.valueOf((int) value);
                                } else {
                                    return String.format("%.2f", value);
                                }
                            }
                        case BOOLEAN:
                            return String.valueOf(cell.getBooleanCellValue());
                        case ERROR:
                            return "#ERROR";
                        default:
                            return cell.getCellFormula();
                    }
                } catch (Exception e) {
                    return cell.getCellFormula();
                }
            case BLANK:
                return "";
            default:
                return "";
        }
    }
}
